package com.sinog.biz.service.business.impl;

import com.sinog.biz.BizApplication;
import com.sinog.biz.service.business.TimingeExecuteSqlService;
import com.sinog.core.util.JsonUtil;
import com.sinog.core.utilbean.Constants;
import com.sinog.core.utilbean.Result;
import com.sinog.core.utilbean.ReturnCode;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

/**
 * @desc 定时清洗实现类
 * @author ybl
 * @date 2022/10/10
 */
@Slf4j
@Service
public class TimingeExecuteSqlServiceImpl implements TimingeExecuteSqlService {

    /**
     * 部级资源库
     */
    private JdbcTemplate bujiJdbcTemplate;

    @Override
    public String selectTimingSql(Map<String,Object> map) {
        // 数据源
        ApplicationContext applicationContext = BizApplication.applicationContext;
        bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
        String sql = "select id, content, remark, carry_result as carryResult, sql_type as sqlType,`type`, province, create_ip as createUser, DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') as createTime from ds_sql where 1=1";
        if(null != map.get("province") && !"".equals(map.get("province"))) {
            sql += " and province like '%" + map.get("province") + "%'";
        }
        if(null != map.get("remark") && !"".equals(map.get("remark"))) {
            sql += " and remark like '%" + map.get("remark") + "%'";
        }
        String sqlCount = "SELECT COUNT(1) FROM ( " + sql + ") AS main";
        sql += " limit " + map.get("pageIndex") + "," + map.get("pageSize");
        log.info(sql);
        List<Map<String,Object>> mapList = bujiJdbcTemplate.queryForList(sql);
        return JsonUtil.toText(bujiJdbcTemplate.queryForObject(sqlCount,int.class),mapList);
    }

    @Override
    public Result delSql(String id) {
        ApplicationContext applicationContext = BizApplication.applicationContext;
        bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
        try {
            bujiJdbcTemplate.update("DELETE FROM ds_sql WHERE id = ?",id);
        } catch(Exception e) {
            log.info(e.getMessage());
            return Result.failure(ReturnCode.DELETE_FAILED);
        }
        return Result.success("删除成功！");
    }

    @Override
    public Result addSql(Map<String,Object> map) {
        //对sql中的 > 、< 这2个符号进行转换
        String content = (String) map.get("content");
        if (content.contains("&gt;") || content.contains("&lt;")){
            content = content.replace("&gt;", ">");
            content = content.replace("&lt;","<");
            map.put("content",content);
        }
        ApplicationContext applicationContext = BizApplication.applicationContext;
        bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
        String sql;
        String mes;
        if(Constants.NumberStr.NUM_STR_ONE.equals(map.get("flag"))) {
            sql = "INSERT INTO ds_sql(`content`,`remark`,`sql_type`,`type`,`province`,`create_ip`) VALUES (\"" + map.get("content") + "\",'" + map.get("remark") + "','" + map.get("sqlType") + "','" + map.get("type") + "','" + map.get("province") + "','" + map.get("ip") + "');";
            mes = "添加成功！";
        } else {
            sql = "UPDATE ds_sql SET content=\"" + map.get("content") + "\",remark='" + map.get("remark") + "',sql_type='" + map.get("sqlType") + "',`type`='" + map.get("type") + "',province='" + map.get("province") + "',create_ip='" + map.get("ip") + "' WHERE id='" + map.get("id") + "';";
            mes = "修改成功！";
        }
        try {
            bujiJdbcTemplate.update(sql);
        } catch(Exception e) {
            log.error(e.getMessage());
            return Result.failure("SQL存在异常");
        }
        return Result.success(mes);
    }
}